﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DCardCheckHistory
    {
        
        public bool Add(Model.CardCheckHistoryInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tCardCheckHistory(");
            strSql.Append("PhoneNumber,CardId,CheckResult,CheckTime)");            
            strSql.Append(" values (");
            strSql.Append("@PhoneNumber,@CardId,@CheckResult,@CheckTime)");            
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@CardId", MySqlDbType.VarChar,10),
					new MySqlParameter("@CheckResult", MySqlDbType.VarChar,10),
                    new MySqlParameter("@CheckTime", MySqlDbType.DateTime)
                  };
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.CardId;
            parameters[2].Value = model.CheckResult;
            parameters[3].Value = model.CheckTime;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public DataSet GetListWithParamater(string companyName,
            string phoneNumber,string startTime, string endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT tA.PhoneNumber,tA.CardId,");
            strSql.Append("(case tA.CheckResult when '0' then '失败' ");
            strSql.Append("when '1' then '成功' end) as CheckResult,");
            strSql.Append("tA.CheckTime,tB.FullName,tD.TaxiNo,");
            strSql.Append("tE.CompanyName ");
            strSql.Append("FROM taxidispatchsystem.tcardcheckhistory tA ");
            strSql.Append("left join dbfingerprint.tdriver tB ");
            strSql.Append("on tA.CardId = tB.ServiceCardNo ");
            strSql.Append("left join taxidispatchsystem.tdriver tC on ");
            strSql.Append("tA.PhoneNumber = tC.PhoneNumber ");
            strSql.Append("left join taxidispatchsystem.ttaxi tD on ");
            strSql.Append("tC.TaxiID = tD.Id ");
            strSql.Append("left join taxidispatchsystem.tCompany tE on ");
            strSql.Append("tD.CompanyId = tE.Id ");
            strSql.Append("where CheckTime between '");
            strSql.Append(startTime);
            strSql.Append("' and '");
            strSql.Append(endTime);
            strSql.Append("' ");          
            if (companyName.Trim()!="")
            {
                strSql.Append("and CompanyName = '");
                strSql.Append(companyName);
                strSql.Append("' ");
            }
            if (phoneNumber.Trim()!="")
            {
                strSql.Append(" and tA.PhoneNumber = '");
                strSql.Append(phoneNumber);
                strSql.Append("' ");
            }
            DataSet  ds =  DbHelperMySQL.Query(strSql.ToString());
            return ds;
        }









    }
}
